package com.taijihuabao.common.excel;


import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.taijihuabao.modules.entity.ChaimDetailEntity;
import com.taijihuabao.modules.model.chaim.vo.ChaimExportVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import java.io.*;
import java.lang.reflect.Field;
import java.util.List;

import static org.apache.poi.ss.usermodel.VerticalAlignment.CENTER;

/**
 * @author xuejingtai
 * Excel导出工具类
 */
public class ExcelUtil {

    /**
     * 保存的数据
     *
     * @param list
     * @return
     */
    public static byte[] export(List list) {
        //创建excel表
        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet = wb.createSheet();
        //设置默认行宽
        sheet.setDefaultColumnWidth(20);
        //给列设置单独格式   以输入字符的1/256作为单元格宽---the width in units of 1/256th of a character width
        sheet.setColumnWidth(1, 60 * 256);
        sheet.setColumnWidth(2, 60 * 256);
        sheet.setColumnWidth(3, 60 * 256);
        sheet.setColumnWidth(4, 60 * 256);
        HSSFFont titleFontStyle = wb.createFont();
        titleFontStyle.setBold(true);

        //标题样式（加粗，垂直居中）
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //垂直居中
        cellStyle.setVerticalAlignment(CENTER);
        //水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFont(titleFontStyle);
        //设置边框样式: 上 下 左 右边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        /**
         * 设置标题
         */
        //新建一行
        HSSFRow rowTitle = sheet.createRow(0);
        //设置高度
        rowTitle.setHeightInPoints(20);
        Object titleObj = list.get(0);
        Field[] fields = titleObj.getClass().getDeclaredFields();
        HSSFCell hc;
        for (int i = 0; i < fields.length; i++) {
            hc = rowTitle.createCell(i);
            hc.setCellValue(fields[i].getName());
            hc.setCellStyle(cellStyle);
        }
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        try {
            //创建表格数据
            int i = 1;
            for (Object obj : list) {
                fields = obj.getClass().getDeclaredFields();
                HSSFRow rowBody = sheet.createRow(i);
                rowBody.setHeightInPoints(20);
                int j = 0;
                for (Field f : fields) {
                    f.setAccessible(true);
                    Object va = f.get(obj);
                    if (null == va) {
                        va = "";
                    }
                    hc = rowBody.createCell(j);
                    hc.setCellValue(va.toString());
                    hc.setCellStyle(cellStyle);
                    j++;
                }
                i++;
            }
            wb.write(byteArrayOutputStream);
            return byteArrayOutputStream.toByteArray();
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                wb.close();
            } catch (IOException ex) {
            }
        }
        return null;
    }


    /**
     * 导出excel
     */
    public static void exportExcel(List<ChaimExportVo> list){
        String path = "C:\\Users\\26638\\Desktop\\statement.xlsx";
        ExcelWriter excelWriter = EasyExcel.write(path,ChaimExportVo.class).build();
        WriteSheet sheet = EasyExcel.writerSheet("chaim").build();
        excelWriter.write(list,sheet);
        excelWriter.finish();
        System.out.println("导出成功");
    }

    /**
     * 读取Excel
     */
/*    public static void readExcel(String fileName){
        ExcelReader build = EasyExcel.read(fileName,ChaimDetailEntity.class,new EasyExcelListerner()).build();
        ReadSheet build1 = EasyExcel.readSheet(0).build();
        build.read(build1);
        build.finish();
    }*/
  /*  public static List<ChaimDetailEntity> readExcel(String fileName) {
        List<ChaimDetailEntity> list = new ArrayList<ChaimDetailEntity>();
        try {
            // 读取Excel文件
            InputStream fileIn = new FileInputStream(fileName);


            Workbook workbook = new HSSFWorkbook(fileIn);
            Sheet hssfSheet = workbook.getSheetAt(0);
            // 循环行
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = (HSSFRow) hssfSheet.getRow(rowNum);
                if (hssfRow == null) {
                    continue;
                }
                // 将单元格中的内容存入集合
                ChaimDetailEntity chaimDetail = new ChaimDetailEntity();

                HSSFCell cell = hssfRow.getCell(1);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setChaimCode(cell.getStringCellValue());

                cell = hssfRow.getCell(2);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setDetailDate(cell.getDateCellValue());

                cell = hssfRow.getCell(3);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setChaimType(cell.getStringCellValue());

                cell = hssfRow.getCell(4);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setDocumentNumber(cell.getStringCellValue());

                cell = hssfRow.getCell(5);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setDetailMoney(cell.getNumericCellValue());

                cell = hssfRow.getCell(6);
                if (cell == null) {
                    continue;
                }
                chaimDetail.setInformation(cell.getStringCellValue());

                list.add(chaimDetail);
            }
            fileIn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }*/
    /*private static boolean judegExcelEdition(String fileName){
        if (fileName.matches("^.+\\.(?i)(xls)$")){
            return false;

        }else {
            return true;

        }
    }*/
}
